package com.cbw.dao.ImpI;

import com.cbw.bean.Express;
import com.cbw.dao.BaseExpressDao;
import com.cbw.exception.DuplicateCodeException;
import com.cbw.util.DruidUtil;
import com.cbw.util.RandomUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class BaseExpressDaoMysql implements BaseExpressDao {
    //SQL语句编写
    //查询数据库的快递(总数和新增)、待取件快递(总数和新增)--控制台部分
    private static final String SQL_CONSOLE="select "+
            "count(id) data1_size,"+  //快递总数
            "count(to_days(intime)=to_days(now()) or null) data1_day,"+//今日新增快递:入库时间=今日时间
            "count(status=0 or null) data2_size,"+//待取件快递总数
            "count(to_days(intime)=to_days(now()) and status=0 or null) data2_day "+//今日待取件总数:入库时间=今日时间 且 快件状态待取
            "from express";
    //查询所有快递信息(不分页)
    private static final String SQL_FIND_ALL="select * from express";
    //查询所有快递信息(分页:limit 当前索引值(当前页码值-1)*每页条数)
    private static final String SQL_FIND_ALL_LIMIT="select * from express limit ?,?";
    //通过取件码查询快递信息
    private static final String SQL_Find_By_CODE="select * from express where code=?";
    //通过快递单号查询快递信息
    private static final String SQL_FIND_By_NUMBER="select * from express where number=?";
    //通过录入人手机号码查询快递信息
    private static final String SQL_FIND_By_SYSPHONE="select * from express where sysphone=?";
    //通过用户手机号查询所有快递信息
    private static final String SQL_FIND_By_USERPHONE="select * from express where userphone=?";
    //通过用户手机号和快递状态查询所有快递信息
    private static final String SQL_FIND_By_USERPHONE_AND_STATUS="select * from express where userphone=? and status=?";
    //快递入柜
    private static final String SQL_INSERT="insert into express(id,number,username,userphone,company,code,intime,status,sysphone) values(null,?,?,?,?,?,now(),0,?)";
    //快递修改(快递单号、姓名、快递公司、状态)--手机号的更改，走删除流程
    private static final String SQL_UPDATE="update express set number=?,username=?,company=?,status=? where id=?";
    //快递的状态码修改(根据取件码取件后，修改该快递状态码为1，出库时间，取件码设为null)
    private static final String SQL_UPDATE_STATUS="update express set status=1,outtime=sysdate() ,code=null where code=?";
    private static final String SQL_UPDATE_STATUS1 ="update express set status=0,outtime=null,code=? where code=?";
    //删除快递
    private static final String SQL_DELETE="delete from express where id=?";


    private static Connection conn=null;
    private static PreparedStatement pps=null;
    private static ResultSet resultSet=null;

    /**
     * 查询数据库的所有快递(总数和当日新增)、待取件快递(总数和今日新增)--控制台部分
     * @return 数组[{data1_size:总数,data1_day:新增},{data2_size:总数,data2_day:新增}]
     */
    @Override
    public List<Map<String, Integer>> console() {
        ArrayList<Map<String,Integer>> data = new ArrayList<>();
        conn = DruidUtil.getConnection();//1.获取数据库连接
        try {
            pps = conn.prepareStatement(SQL_CONSOLE);//预编译sql语句
            resultSet = pps.executeQuery();
            if (resultSet.next()){
                int data1_size = resultSet.getInt("data1_size");//总快件数
                int data1_day = resultSet.getInt("data1_day");//今日新增快递件数
                int data2_size = resultSet.getInt("data2_size");//待取快件总数
                int data2_day = resultSet.getInt("data2_day");//今日待取快件总数
                HashMap map = new HashMap();
                map.put("data1_size",data1_size);
                map.put("data1_day",data1_day);
                HashMap map2 = new HashMap();
                map2.put("data2_size",data2_size);
                map2.put("data2_day",data2_day);
                data.add(map);
                data.add(map2);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,pps,resultSet);
        }
        return data;
    }

    /**
     * 查询所有快递
     * @param limit 是否分页的标记,true表示分页
     * @param offset sql语句的起始索引
     * @param pageNumber 每页查询数量
     * @return
     */
    @Override
    public List<Express> findAll(boolean limit, int offset, int pageNumber) {
        ArrayList <Express>list = new ArrayList();
        conn = DruidUtil.getConnection();//1.获取数据库连接
        try {
            if (limit){ //查询所有快递--分页展示
                pps = conn.prepareStatement(SQL_FIND_ALL_LIMIT);//预编译sql语句
                pps.setInt(1,offset);//填充参数
                pps.setInt(2,pageNumber);
            }else{ //不分页
                pps = conn.prepareStatement(SQL_FIND_ALL);//预编译sql语句
            }
            resultSet = pps.executeQuery();//执行sql
            while(resultSet.next()){
                int id=resultSet.getInt("id");
                String number=resultSet.getString("number");
                String username=resultSet.getString("username");
                String userPhone=resultSet.getString("userPhone");
                String company=resultSet.getString("company");
                String code=resultSet.getString("code");
                Timestamp inTime=resultSet.getTimestamp("intime");
                Timestamp outTime=resultSet.getTimestamp("outTime");
                int status=resultSet.getInt("status");;
                String sysPhone=resultSet.getString("sysphone");
                Express e=new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
                System.out.println("分页查询:"+e);
                list.add(e);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,pps,resultSet);
        }
        return list;
    }

    /**
     * 根据快递单号，查询快递信息
     * @param number
     * @return
     */
    public Express findByNumber(String number){
        conn = DruidUtil.getConnection();//1.获取数据库连接
        try {
            pps = conn.prepareStatement(SQL_FIND_By_NUMBER);//预编译sql语句
            pps.setString(1,number);//填充参数
            resultSet = pps.executeQuery();
            if (resultSet.next()){
                int id=resultSet.getInt("id");
                String username=resultSet.getString("username");
                String userPhone=resultSet.getString("userPhone");
                String company=resultSet.getString("company");
                String code=resultSet.getString("code");
                Timestamp inTime=resultSet.getTimestamp("intime");
                Timestamp outTime=resultSet.getTimestamp("outtime");
                int status=resultSet.getInt("status");;
                String sysPhone=resultSet.getString("sysphone");
                Express e=new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
                return e;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,pps,resultSet);
        }
        return null;
    }

    /**
     * 根据快递取件码，查询快递信息
     * @param code 快递取件码
     * @return  返回查询的快递信息，快递取件码不存在时返回null
     */
    @Override
    public Express findByCode(String code) {
        conn = DruidUtil.getConnection();//1.获取数据库连接
        try {
            pps = conn.prepareStatement(SQL_Find_By_CODE);//预编译sql语句
            pps.setString(1,code);//填充参数
            resultSet = pps.executeQuery();
            if (resultSet.next()){
                int id=resultSet.getInt("id");
                String username=resultSet.getString("username");
                String number=resultSet.getString("number");
                String userPhone=resultSet.getString("userPhone");
                String company=resultSet.getString("company");
                Timestamp inTime=resultSet.getTimestamp("intime");
                Timestamp outTime=resultSet.getTimestamp("outtime");
                int status=resultSet.getInt("status");;
                String sysPhone=resultSet.getString("sysphone");
                Express e=new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
                return e;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,pps,resultSet);
        }
        return null;
    }
    /**
     * 根据用户手机号，查询所有快递信息
     * @param userPhone 用户手机号
     * @return 返回查询的快递信息，用户手机号不存在时返回null
     */
    @Override
    public List<Express> findByUserPhone(String userPhone) {
        ArrayList<Express> list = new ArrayList();
        conn = DruidUtil.getConnection();//1.获取数据库连接
        try {
            pps = conn.prepareStatement(SQL_FIND_By_USERPHONE);//预编译sql语句
            pps.setString(1,userPhone);//填充参数
            resultSet = pps.executeQuery();
            while (resultSet.next()){
                int id=resultSet.getInt("id");
                String username=resultSet.getString("username");
                String number=resultSet.getString("number");
                String company=resultSet.getString("company");
                String code=resultSet.getString("code");
                Timestamp inTime=resultSet.getTimestamp("intime");
                Timestamp outTime=resultSet.getTimestamp("outtime");
                int status=resultSet.getInt("status");;
                String sysPhone=resultSet.getString("sysphone");
                list.add(new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,pps,resultSet);
        }
        return list;
    }

    @Override
    public List<Express> findByUserPhoneAndStatus(String userPhone, int status) {
        ArrayList<Express> list = new ArrayList();
        conn = DruidUtil.getConnection();//1.获取数据库连接
        try {
            pps = conn.prepareStatement(SQL_FIND_By_USERPHONE_AND_STATUS);//预编译sql语句
            pps.setString(1,userPhone);//填充参数
            pps.setInt(2,status);
            resultSet = pps.executeQuery();
            while (resultSet.next()){
                int id=resultSet.getInt("id");
                String username=resultSet.getString("username");
                String number=resultSet.getString("number");
                String company=resultSet.getString("company");
                String code=resultSet.getString("code");
                Timestamp inTime=resultSet.getTimestamp("intime");
                Timestamp outTime=resultSet.getTimestamp("outtime");
                //int status=resultSet.getInt("status");;
                String sysPhone=resultSet.getString("sysphone");
                list.add(new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,pps,resultSet);
        }
        return list;

    }

    /**
     * 根据录入人(快递员)手机号，查询录入的所有记录
     * @param sysPhone 用户手机号
     * @return  返回查询的快递信息，用户手机号不存在时返回null
     */
    @Override
    public List<Express> findBySysNumber(String sysPhone) {
        ArrayList<Express> list = new ArrayList();
        conn = DruidUtil.getConnection();//1.获取数据库连接
        try {
            pps = conn.prepareStatement(SQL_FIND_By_SYSPHONE);//预编译sql语句
            pps.setString(1,sysPhone);//填充参数
            resultSet = pps.executeQuery();
            while (resultSet.next()){
                int id=resultSet.getInt("id");
                String username=resultSet.getString("username");
                String number=resultSet.getString("number");
                String userPhone=resultSet.getString("userPhone");
                String company=resultSet.getString("company");
                String code=resultSet.getString("code");
                Timestamp inTime=resultSet.getTimestamp("intime");
                Timestamp outTime=resultSet.getTimestamp("outtime");
                int status=resultSet.getInt("status");;
                list.add(new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,pps,resultSet);
        }
        return list;
    }
    /**
     * 快递的录入
     * insert into express(id,number,username,userphone,company,code,intime,status,sysphone) values(null,?,?,?,?,?,now(),0,?)"
     * 注意:取件码重复，抛出异常，在service中再重新生成取件码
     * @param e 要录入的快递对象
     * @return 录入结果，true表示成功
     */
    @Override
    public boolean insert(Express e) throws DuplicateCodeException {
        conn = DruidUtil.getConnection();//1.获取数据库连接
        try {
            pps = conn.prepareStatement(SQL_INSERT);//预编译sql语句
            pps.setString(1,e.getNumber());//填充参数(单号和取件码不能重复)
            pps.setString(2,e.getUsername());
            pps.setString(3,e.getUserPhone());
            pps.setString(4,e.getCompany());
            pps.setString(5,e.getCode());
            pps.setString(6,e.getSysPhone());
            return pps.executeUpdate()>0?true:false; //执行sql,获取执行结果
        } catch (SQLException e1) {//取件码重复异常
            System.out.println(e1.getMessage());//若取件码重复，则报错:Duplicate entry '666666' for key 'express.code'
            if (e1.getMessage().endsWith("for key 'express.code'")){
                //取件码重复,自定义异常
                DuplicateCodeException e2 = new DuplicateCodeException(e1.getMessage());
                throw e2;
            }else {
                e1.printStackTrace();
            }
        } finally {
            DruidUtil.close(conn,pps,null);//释放资源
        }
        return false;
    }

    /**
     * 通过id修改快递
     * update express set number=?,username=?,company=?,status=? where id=?
     * @param id 要修改的快递id
     * @param newExpress 新的快递对象(number,company,username,userPhone)
     * @return
     */
    @Override
    public boolean update(int id, Express newExpress) {
        conn = DruidUtil.getConnection();//1.获取数据库连接
        try {
            pps = conn.prepareStatement(SQL_UPDATE);//预编译sql语句
            pps.setString(1,newExpress.getNumber());//填充参数
            pps.setString(2,newExpress.getUsername());
            pps.setString(3,newExpress.getCompany());
            pps.setInt(4,newExpress.getStatus());
            pps.setInt(5,id);
            return pps.executeUpdate()>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,pps,null);
        }
        return false;
    }

    /**
     * 快递的状态码修改--取件(根据取件码取件后，修改该快递状态码为1，出库时间，取件码设为null)
     * update express set status=1,outtime=now(),code=null where code=?
     * @param code 待取件的取件码
     * @return 修改结果 true表示取件成功
     */
    @Override
    public boolean updateStatus(String code) {
        conn = DruidUtil.getConnection();//1.获取数据库连接
        try {
            pps = conn.prepareStatement(SQL_UPDATE_STATUS);//预编译sql语句
            pps.setString(1,code);//填充参数
            return pps.executeUpdate()>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,pps,null);
        }
        return false;
    }

    /**快递的状态码修改
     * 出库时间清空、重新获取取件码，快递状态改为未取件
     * update express set status=0,outtime=null,code=? where code=?"
     * @param code
     * @return
     */
    @Override
    public boolean updateStatus1(String code) {
        conn = DruidUtil.getConnection();//1.获取数据库连接
        try {
            pps = conn.prepareStatement(SQL_UPDATE_STATUS1);//预编译sql语句
            String code1=RandomUtil.getCode()+"";
            pps.setString(1,code1);//填充参数
            pps.setString(2,code);
            return pps.executeUpdate()>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,pps,null);
        }
        return false;
    }

    /**
     * 根据id,删除个人快递信息
     * @param id
     * @return
     */
    @Override
    public boolean delete(int id) {
        conn = DruidUtil.getConnection();//1.获取数据库连接
        try {
            pps = conn.prepareStatement(SQL_DELETE);//预编译sql语句
            pps.setInt(1,id);//填充参数
            return pps.executeUpdate()>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,pps,null);
        }
        return false;
    }


}
